import pandas as pd
import numpy as np
import MySQLdb
import re
import sys
import time

conn = MySQLdb.connect("localhost", "root", "zhaoming832002", "proxy", charset='utf8')
cursor = conn.cursor()
sql = "SELECT a.*,b.* FROM proxy.fanghouseinfo as a, proxy.fangcatalog as b where a.idcatalog=b.id  "
sql = "SELECT a.*,b.* FROM proxy.fanghouseinfo as a, proxy.fangcatalog as b where a.idcatalog=b.id and b.cityname='长沙' "
cursor.execute(sql)
c = [x[0] for x in cursor.description]
rows = cursor.fetchall()
df = pd.DataFrame(list(rows), columns=[x[0] for x in cursor.description])
df = df.drop(["idCatalog", "id", "urlNewHouse", "urlSencondHandHouse", "urlRentalhousing"], 1)
# df[2:4]
columnReindex = ["collecttime","ProvinceName", "CityName", "type", "name", "address", "housetype", "characteristic", "telephone",
                 "price"]
# df.rename(columns = columnReindex)
df = df[columnReindex]
dfOrign = df.copy()
# df["houseHomeNum"] = df["housetype"].str.split('－')
conditions = [df['type'] == 0, df['type'] == 1, df['type'] == 2]
veSplit = [df["housetype"].str.split('－', expand=True), df["housetype"].str.split('|', expand=True),
           df["housetype"].str.split('|', expand=True)]

choices = []
for i in range(2):
    choices.append([veSplit[0][i].str.strip(), veSplit[1][i].str.strip(), veSplit[2][i + 1].str.strip()])
df["houseHomeNum"] = np.select(conditions, choices[0], default='')
df["houseSize"] = np.select(conditions, choices[1], default='')
df = df[df["type"] == 0]
df.reset_index(inplace=True, drop=True)

df["houseHomeNum"] = df["houseHomeNum"].str.replace("居", "室")

vMatch = df["address"].apply(lambda x: re.search("^\[[^\x00-\xff]+\]", x))

for i in vMatch.index:
    if vMatch[i] != None:
        vMatch[i] = vMatch[i].group()
    else:
        vMatch[i] = ""

df["region"] = vMatch

vMatch = df["houseHomeNum"].str.match("\d室|\d厅")
df = df.drop(index=[i for i in vMatch.index if vMatch[i] == False])
vMatch = df["price"].str.match("^\d+元/㎡")
df = df.drop(index=[i for i in vMatch.index if vMatch[i] == False])

df = df.drop(index=[i for i in df["price"].index if df["price"][i] == ""])

df = df.drop(index=[i for i in df["region"].index if df["region"][i] == ""])

df["price"] = df["price"].str.replace("起", "")
df["销售类型"] = df["characteristic"].str[0:2]
##
df["住宅类型"] = np.select(
    [
        df["characteristic"].str.find("普通住宅") != -1,
        df["characteristic"].str.find("公寓") != -1,
        df["characteristic"].str.find("写字楼") != -1,
        df["characteristic"].str.find("别墅") != -1,
        df["characteristic"].str.find("商铺") != -1,
        df["characteristic"].str.find("商住楼") != -1,
        True
    ],
    [
        "普通住宅",
        "公寓",
        "写字楼",
        "别墅",
        "商铺",
        "商住楼",
        "其他"
    ],

    default='')

df["dateTime"] = df["collecttime"].apply(lambda x: time.strftime("%Y-%m-%d",  time.gmtime(x)))
df.reset_index(inplace=True, drop=True)
dfnew = df[["dateTime", "ProvinceName", "CityName", "region", "住宅类型", "name", "houseHomeNum", "houseSize", "销售类型", "price"]]
dfnew = dfnew.sort_values(by=["dateTime","ProvinceName", "CityName", "region", "住宅类型"]).copy()
dfnew.reset_index(inplace=True, drop=True)
dfnew["price"] = dfnew["price"].str.replace("元/㎡", "")

vMatch = dfnew["price"].str.match("^\d+$")
dfnew = dfnew.drop(index=[inx for inx in vMatch.index if vMatch[inx] == False])
dfnew["price"] = dfnew["price"].astype('float')

# dfnew
# index=[[],[],[],[], []]
# for i in range(len(dfnew)):
#    index[0].append(dfnew["ProvinceName"][i])
#    index[1].append(dfnew["CityName"][i])
#    index[2].append(dfnew["region"][i])
#    index[3].append(dfnew["住宅类型"][i])
#    index[4].append(i)
# dfnew.index = index
# dfnew = dfnew[ ["name", "houseHomeNum", "houseSize", "销售类型", "price"]]
# dfnew.index
# dfnew = dfnew.set_index(["ProvinceName", "CityName", "region", "住宅类型"])
# dfnew
# temp = dfnew.loc["湖南", "长沙", "[岳麓]"]
# dfnew.count("region", axis=1)

# dfnew["price"].describe()



dfnew.to_csv("d:\\1手房.csv")
dfnewGroup = dfnew.groupby(["dateTime", "ProvinceName", "CityName", "region", "住宅类型"])
c = dfnew.groupby(["ProvinceName", "CityName", "region", "住宅类型", "dateTime"])["price"].mean()
c.to_csv("d:\\1.csv")
# dfnewGroup("price").mean()